package com.itheima.mapper;

import com.github.pagehelper.Page;
import com.itheima.pojo.StuClazzCount;
import com.itheima.pojo.Student;
import com.itheima.pojo.StudentQueryParam;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * @version 1.0
 * @description 说明
 * @package com.itheima.mapper
 */
@Mapper
public interface StudentMapper {

    @Select("select count(*) from student where clazz_id=#{clazzId} limit 1")
    Integer existsWithClazzId(Integer clazzId);

    Page<Student> pageQuery(StudentQueryParam studentQueryParam);

    @Insert("insert into student (name,no,gender,phone,id_card,is_college,address,degree,graduation_date,clazz_id,violation_count,violation_score,create_time,update_time) " +
        "values(#{name},#{no},#{gender},#{phone},#{idCard},#{isCollege},#{address},#{degree},#{graduationDate},#{clazzId},0,0,#{createTime},#{updateTime})")
    void insert(Student student);

    @Select("select * from student where id=#{id}")
    Student selectById(Integer id);

    void updateById(Student student);

    @Delete("delete from student where id=#{id}")
    void deleteById(Integer id);

    @Update("update student set violation_count=violation_count+1, violation_score=violation_score+#{score} where id=#{id}")
    Integer updateViolation(@Param("id") Integer id, @Param("score") Integer score);

    @Select("select ifnull(z.name,'其它') clazzName, count(*) count from student s left join clazz z on s.clazz_id=z.id group by clazz_id,z.name")
    List<StuClazzCount> countByClazz();

    List<Map<String, Object>> studentDegreeData();

    @Select("select count(1) from student where clazz_id=1")
    Long count();
}
